﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	待审批临时补助
-- =============================================
CREATE PROCEDURE [dbo].[proc_Employee_Subsidy_ApplyList] 
	(
	@startIndex int,
	@endindex int,
	@ename nvarchar(50),
	@companyid varchar(2000),
	@audit varchar(10)
	
	)
AS
BEGIN
	declare @SQL nvarchar(4000)
	
	set @SQL='  eid<>0 '
	
	if @companyid<>'0'
	begin
		set @SQL=@SQL+' and companyid in ('+@companyid+') '
	end
	
	if(@ename<>'')
	begin
		set @SQL=@SQL+' and  ename like ''%'+@ename+'%'' '
	end
	if(@audit<>'%')
	begin
	set @SQL=@SQL+' and  flag= '+@audit
	end
	
	set @SQL=';WITH list as (select ROW_NUMBER() OVER (Order By ApplionMonth desc,FirstAuditDate,ApplionTime DESC)AS Row,[EName],eid,id,gid,
	[Companyid],[ApplionMoney],[ApplionMonth],flag,ProposerName,FirstAuditPerson,FirstAuditPersonName,FirstMemo,Reason ,isnull(FirstAuditDate,'''') FirstAuditDate,departmentid
					,(case companyname when '''' then (select companyname from company where company.id=companyid) else companyname end) as companyname
			  ,(case DepartmentName when '''' then (select DName from Department where id=departmentid) else DepartmentName end) as DepartmentName	from Employee_Subsidy where  '+@SQL+')
						
			  select *
			  ,(select count(1) from list ) as coun
			  
			   from list where row >='+Convert(varchar,@startIndex)+' and row <= '+Convert(varchar,@endindex)+' 
						
						
						'
	
	
	exec(@SQL)
	
	
END
